﻿//sqlitedatabase.cpp
#include "SqliteDatabase.h"
#include <QDebug>
#include <QJsonDocument>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QThread>
#include <QApplication>
#include <QSqlError>
#include <QSqlDriver>
#include <QTimer>

class SqliteDatabase::Implementation
{
public:

    Implementation(){}

    QSqlDatabase database;
    QString mTableName;
    QString mDatabaseType;
    QString mDatabaseName;
public:
    void initdatabase()
    {
        if (initialise()) {
            qDebug() << mDatabaseType << "Database created using version: " + version();
            if (createTables()) {
                qDebug() << mDatabaseType << " Database tables created";
            } else {
                qDebug() << "ERROR: Unable to create database tables";
            }
        } else {
            qDebug() << "ERROR: Unable to open database";
        }
    }

private:

    bool initialise()
    {
        auto connectName = "my_db_" + QString::number((quint64)QThread::currentThread(), 16);
        database = QSqlDatabase::addDatabase(mDatabaseType,connectName);
        if(mDatabaseType == "QMYSQL"){
            database.setHostName("127.0.0.1");
            database.setPort(3306);
            database.setUserName("root");
            database.setPassword("iv2016");
            if(database.open()){
                QSqlQuery query(database);
                QString sqlStatement = "CREATE DATABASE IF NOT EXISTS " + mDatabaseName;
                if (!query.prepare(sqlStatement)){
                    qDebug() << query.lastError().text();
                    return false;
                }
                if (!query.exec()){
                    qDebug() << query.lastError().text();
                    return false;
                }
                database.setDatabaseName(mDatabaseName);
                if(!database.open())
                {
                    qDebug() << "fail to connect mysql:" + database.lastError().text();
                    return false;
                }
                return true;
            } else {
                return false;
            }
        } else {
            database.setDatabaseName(mDatabaseName);
            if(database.open()){
                return true;
            } else {
                qDebug() << database.lastError().text();
                return false;
            }
        }
    }

    //如果表不存在的话创建对应的表
    bool createTables()
    {
        return createJsonTable(mTableName);
    }
    bool createJsonTable(const QString& tableName) const
    {
        QSqlQuery query(database);
        QString sqlStatement;
        if(mDatabaseType == "QSQLITE"){
            sqlStatement = "CREATE TABLE IF NOT EXISTS " + tableName + " (id text primary key, json text not null)";
        } else {
            sqlStatement = "CREATE TABLE IF NOT EXISTS " + tableName + " (id varchar(256) primary key, json text not null)";
        }
        if (!query.prepare(sqlStatement)){
            qDebug() << "createJsonTable prepare" << query.lastError().text();
            return false;
        }
        if(query.exec()){
            return true;
        } else {
            qDebug() << "createJsonTable exec" << query.lastError().text();
            return false;
        }
    }

    //获取Sqlite的版本
    QString version() const
    {
        QSqlQuery query(database);
        if(mDatabaseType == "QSQLITE"){
            query.exec("SELECT sqlite_version()");
        } else {
            query.exec("SELECT version()");
        }
        if (query.next()) {
            return query.value(0).toString();
        } else {
            return QString::number(-1);
        }
    }
};

SqliteDatabase::SqliteDatabase(QObject *parent, QString databaseName, QString tableName, QString databaseType):IDatabase(parent)
{
    implementation.reset(new Implementation());
    //设置数据库的名称和表的名称
    implementation->mTableName = tableName;
    implementation->mDatabaseName = databaseName;
    implementation->mDatabaseType = databaseType;

    //初始化数据库
    implementation->initdatabase();
    connectName = implementation->database.connectionName();

    if(databaseType == "QMYSQL"){
        timCheckStatus = new QTimer(this);
        connect(timCheckStatus, &QTimer::timeout, this, [&](){
            QSqlQuery query(implementation->database);
            QString sqlStatement = "SELECT json FROM " + implementation->mTableName + " WHERE id=:id";
            if (!query.prepare(sqlStatement)){
                qDebug() << "prepare error";
                implementation->initdatabase();
            }
        });
        timCheckStatus->start(10000);
    }

}

SqliteDatabase::~SqliteDatabase()
{

}

//插入一条新数据
bool SqliteDatabase::createRow(const QString& tableName, const QString& id, const QJsonObject& jsonObject) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;
    if (jsonObject.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "INSERT  INTO " + tableName + " (id, json) VALUES (:id, :json)";
    if (!query.prepare(sqlStatement)) {
        qDebug() << "createRow error" << query.lastError().text();
        return false;
    }

    query.bindValue(":id", QVariant(id));
    query.bindValue(":json", QVariant(QJsonDocument(jsonObject).toJson(QJsonDocument::Compact)));

    if(!query.exec()) {
        qDebug() << "createRow " << query.lastError().text();
        return false;
    }

    return query.numRowsAffected() > 0;
}

//根据ID删除某条数据
bool SqliteDatabase::deleteRow(const QString& tableName, const QString& id) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "DELETE FROM " + tableName + " WHERE id=:id";

    if (!query.prepare(sqlStatement)) return false;

    query.bindValue(":id", QVariant(id));

    if(!query.exec()) return false;

    return query.numRowsAffected() > 0;
}

//通过文本查找对应的数据
QJsonArray SqliteDatabase::find(const QString& tableName, const QString& searchText) const
{
    if (tableName.isEmpty()) return {};
    if (searchText.isEmpty()) return {};

    QSqlQuery query(implementation->database);

    QString sqlStatement = "SELECT json FROM " + tableName + " where lower(json) like :searchText";

    if (!query.prepare(sqlStatement)) return {};

    query.bindValue(":searchText", QVariant("%" + searchText.toLower() + "%"));

    if (!query.exec()) return {};

    QJsonArray returnValue;

    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }

    return returnValue;
}

//根据ID查找对应的数据
QJsonObject SqliteDatabase::readRow(const QString& tableName, const QString& id) const
{
    if (tableName.isEmpty()) return {};
    if (id.isEmpty()) return {};

    QSqlQuery query(implementation->database);

    QString sqlStatement = "SELECT json FROM " + tableName + " WHERE id=:id";

    if (!query.prepare(sqlStatement)) return {};

    query.bindValue(":id", QVariant(id));

    if (!query.exec()) return {};

    if (!query.first()) return {};

    auto json = query.value(0).toByteArray();
    auto jsonDocument = QJsonDocument::fromJson(json);

    if (!jsonDocument.isObject()) return {};

    return jsonDocument.object();
}

QJsonArray SqliteDatabase::readAllRows(const QString &tableName) const
{
    if (tableName.isEmpty()) return {};

    QSqlQuery query(implementation->database);

    QString sqlStatement = "SELECT json FROM " + tableName;

    if (!query.prepare(sqlStatement)) return {};
    if (!query.exec()) return {};

    QJsonArray returnValue;

    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }

    return returnValue;
}

//通过ID来更新对应的数据结构
bool SqliteDatabase::updateRow(const QString& tableName, const QString& id, const QJsonObject& jsonObject) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;
    if (jsonObject.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "UPDATE " + tableName + " SET json=:json WHERE id=:id";

    if (!query.prepare(sqlStatement)) return false;

    query.bindValue(":id", QVariant(id));
    query.bindValue(":json", QVariant(QJsonDocument(jsonObject).toJson(QJsonDocument::Compact)));

    if(!query.exec()) return false;

    return query.numRowsAffected() > 0;
}
